


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;


/* @Sangeeta
 * @Uses: I need No of bug reports w.r.t each version for putting it in appendix.
 **/
public class get_appendix_version 
{

	public static void main(String args[])
	{
		get_appendix_version gav =  new get_appendix_version();
		Connection conn =  null;
		db_conn db  =  new db_conn();
		conn =  db.connect("2jboss", "root", "123");
		gav.create_appendix(conn);
	}

	private  void create_appendix(Connection conn) 
	{
	
		 Statement stmt =  null;
		 Vector<String> selected_bugs  = new Vector<String>();
		 String str  = "select bugid from selected_bugs";
		 try 
		 {
			stmt   =  conn.createStatement();
			stmt.execute(str);
			ResultSet rs   = stmt.getResultSet();
			while(rs.next())
			{
				String bugid = rs.getString("bugid");
				selected_bugs.add(bugid);
			}
			
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
		  } catch (SQLException e)
		  {	e.printStackTrace(); }
		 
	
		  for(int i=0;i<selected_bugs.size();i++)
		  {
			  String bugid  = selected_bugs.get(i);
			   
			 // Vector<String> versions = new Vector<String>();
			  //Although this complicated query is not needed, but I am inserting I dont know...:D
			  // and version in (select distinct version from selected_version_for_4)"; This additinal quer can help but I am not using to make it consists with the h2_opt.java
				
			  String sq =  "select version from bug_version where bugid='"+bugid+"'";
			  try {
				stmt  = conn.createStatement();
				stmt.execute(sq);
				ResultSet rs =  stmt.getResultSet();
				
				
				String version = "";
				 while(rs.next())
				 {
					 version = rs.getString("version");
					 boolean valid =isValidVesion(version,conn); 
					 if(valid)
					 {
						 break;
					 }
				 }//wh
			 
				 String in = "insert into appendix1 values('"+bugid+"','"+version+"')";
				 Statement stmt2 =  null;
				 stmt2 =  conn.createStatement();
				 stmt2.execute(in);
				 
				 if(stmt2!=null)
					 stmt2.close();
				 
				 if(rs!=null)
						rs.close();
					 
				     if(stmt!=null)
					   stmt.close(); 
			
				
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		  }
	}
	
	
	private boolean isValidVesion(String version, Connection conn) 
	{
	    Statement stmt =  null;
	    String v_q="select version from  selected_version_for_4";
	    Vector<String> selected_versions =  new Vector<String>();
	    
	    try
	     {
	    	stmt = conn.createStatement();
			stmt.execute(v_q);
			ResultSet rs =  stmt.getResultSet();
			
			while(rs.next())
			 {
			  selected_versions.add(rs.getString("version")); 	
			 }
			
			if(rs!=null)
				rs.close();
			if(stmt!=null)
				stmt.close();
		 } catch (SQLException e)
		 { 		e.printStackTrace(); 	}    
		 
		 //for
	     boolean flag =  false;
		 for(int i=0;i<selected_versions.size();i++)
		 {
			 if(version.equalsIgnoreCase(selected_versions.get(i)))
			 {
				  flag=  true;
			 }//if
		 }
		 
		return flag; 
		
	}//isValidVesion

	
}//class
